package com.han.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import com.han.bom.Exam;
import com.han.bom.PracticeExam;
import com.han.bom.RealExam;

public class ExamDAO {
	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(ExamDAO.class);
	
	public void connect() throws ClassNotFoundException, SQLException{
		try{
			Class.forName("com.mysql.jdbc.Driver");
			this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/beta_test_engine", "root", "");
		}catch(ClassNotFoundException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		}
	}
	
	public void disconnect() throws SQLException{
		if(this.resultSet!=null){
			try{
				this.resultSet.close();
			}catch(SQLException e){
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.preparedStatement!=null){
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
		
		if(this.connection!=null){
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}

	public ArrayList<Exam> retrieveAllExams() throws ClassNotFoundException, SQLException{
		ArrayList<Exam> exams = new ArrayList<Exam>();
		RealExam re = null;
		PracticeExam pe = null;
		Boolean type = null;
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean("exam_type");
				
				if(type){
					re = new RealExam();
					re.setExamID("E"+this.resultSet.getInt("exam_id")+"RE");
					re.setExamName(this.resultSet.getString("exam_name"));
					re.setDuration(this.resultSet.getLong("exam_duration"));
					re.setPassingPercent(this.resultSet.getDouble("exam_passing"));
					re.setActivated(this.resultSet.getBoolean("exam_active"));
					re.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					
					exams.add(re);
				}else{
					pe = new PracticeExam();
					pe.setExamID("E"+this.resultSet.getInt("exam_id")+"PE");
					pe.setExamName(this.resultSet.getString("exam_name"));
					pe.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					
					exams.add(pe);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return exams;
	}
	
	public Exam retrieveExamById(String examid) throws ClassNotFoundException, SQLException{
		Exam exam = null;
		Boolean type = null;
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam WHERE exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean("exam_type");
				
				if(type){
					exam = new RealExam();
					exam.setExamID("E"+this.resultSet.getInt("exam_id")+"RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet.getInt("activate_count"));
				}else{
					exam = new PracticeExam();
					exam.setExamID("E"+this.resultSet.getInt("exam_id")+"PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return exam;
	}
	
	public ArrayList<Exam> retrieveExamsByProfessor(String profid) throws ClassNotFoundException, SQLException{
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		RealExam re = null;
		PracticeExam pe = null;
		Boolean type = null;
		
		try{
			id = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam WHERE professor_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean("exam_type");
				
				if(type){
					re = new RealExam();
					re.setExamID("E"+this.resultSet.getInt("exam_id")+"RE");
					re.setExamName(this.resultSet.getString("exam_name"));
					re.setDuration(this.resultSet.getLong("exam_duration"));
					re.setPassingPercent(this.resultSet.getDouble("exam_passing"));
					re.setActivated(this.resultSet.getBoolean("exam_active"));
					re.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) re).setTimesActivated(this.resultSet.getInt("activate_count"));
					
					exams.add(re);
				}else{
					pe = new PracticeExam();
					pe.setExamID("E"+this.resultSet.getInt("exam_id")+"PE");
					pe.setExamName(this.resultSet.getString("exam_name"));
					pe.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					
					exams.add(pe);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return exams;
	}
	
	public ArrayList<Exam> retrieveExamsByStudent(String studentid) throws ClassNotFoundException, SQLException{
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		RealExam re = null;
		PracticeExam pe = null;
		Boolean type = null;
		
		try{
			id = Integer.parseInt(studentid.substring(1));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam AS a INNER JOIN tbl_student_exam AS b ON a.exam_id = b.exam_id WHERE b.student_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean("exam_type");
				
				if(type){
					re = new RealExam();
					re.setExamID("E"+this.resultSet.getInt("exam_id")+"RE");
					re.setExamName(this.resultSet.getString("exam_name"));
					re.setDuration(this.resultSet.getLong("exam_duration"));
					re.setPassingPercent(this.resultSet.getDouble("exam_passing"));
					re.setActivated(this.resultSet.getBoolean("exam_active"));
					re.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) re).setTimesActivated(this.resultSet.getInt("activate_count"));
					
					exams.add(re);
				}else{
					pe = new PracticeExam();
					pe.setExamID("E"+this.resultSet.getInt("exam_id")+"PE");
					pe.setExamName(this.resultSet.getString("exam_name"));
					pe.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					
					exams.add(pe);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return exams;
	}
	
	public ArrayList<Exam> retrieveExamsByKeyword(String key, String profId) throws ClassNotFoundException, SQLException{
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int profid = 0;
		RealExam re = null;
		PracticeExam pe = null;
		Boolean type = null;
		String intkey = "%"+key+"%";
		
		try{
			id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try{
			profid = Integer.parseInt(profId.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			profid = 0;
		}
		
		try {
			this.connect();
			String select = "SELECT DISTINCT * FROM tbl_exam AS a INNER JOIN tbl_exam_questions AS b ON a.exam_id=b.exam_id INNER JOIN tbl_question AS c ON b.question_id=c.question_id INNER JOIN tbl_choice AS d ON c.question_id=d.question_id WHERE a.professor_id = ? AND (a.exam_id = ? OR a.exam_name LIKE ? OR c.question LIKE ? OR c.question_category LIKE ? OR d.answer LIKE ?)";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, profid);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.preparedStatement.setString(5, intkey);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				type = this.resultSet.getBoolean("exam_type");
				
				if(type){
					re = new RealExam();
					re.setExamID("E"+this.resultSet.getInt("exam_id")+"RE");
					re.setExamName(this.resultSet.getString("exam_name"));
					re.setDuration(this.resultSet.getLong("exam_duration"));
					re.setPassingPercent(this.resultSet.getDouble("exam_passing"));
					re.setActivated(this.resultSet.getBoolean("exam_active"));
					re.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) re).setTimesActivated(this.resultSet.getInt("activate_count"));
					
					exams.add(re);
				}else{
					pe = new PracticeExam();
					pe.setExamID("E"+this.resultSet.getInt("exam_id")+"PE");
					pe.setExamName(this.resultSet.getString("exam_name"));
					pe.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					
					exams.add(pe);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return exams;
	}
	
	public boolean insertExam(Exam exam, String profid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "";
		int pid = 0;
		
		try{
			pid = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			pid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			if(exam instanceof RealExam){
				insert = "INSERT INTO tbl_exam(exam_name,exam_duration,exam_passing,exam_type,exam_active,exam_is_draft,professor_id) VALUES(?,?,?,?,?,?,?)";
				this.preparedStatement = this.connection.prepareStatement(insert);
				
				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setLong(2, exam.getDuration());
				this.preparedStatement.setDouble(3, exam.getPassingPercent());
				this.preparedStatement.setBoolean(4, true);
				this.preparedStatement.setBoolean(5, ((RealExam) exam).isActivated());
				this.preparedStatement.setBoolean(6, exam.isDraft());
				this.preparedStatement.setInt(7, pid);
			}else if(exam instanceof PracticeExam){
				insert = "INSERT INTO tbl_exam(exam_name,exam_type,exam_active,exam_is_draft,professor_id) VALUES(?,?,?,?,?)";
				this.preparedStatement = this.connection.prepareStatement(insert);
				
				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setBoolean(2, false);
				this.preparedStatement.setBoolean(3, true);
				this.preparedStatement.setBoolean(4, exam.isDraft());
				this.preparedStatement.setInt(5, pid);
			}
			
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateExam(Exam exam) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String update = "";
		this.connect();
		int examid = 0;
		
		try{
			examid = Integer.parseInt(exam.getExamID().replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			examid = 0;
		}
		
		try {
			this.connection.setAutoCommit(false);
			if(exam instanceof RealExam){
				update = "UPDATE tbl_exam SET exam_name=?,exam_duration=?,exam_passing=?,exam_type=?,exam_active=?,exam_is_draft=? WHERE exam_id=?";
				this.preparedStatement = this.connection.prepareStatement(update);
				
				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setLong(2, exam.getDuration());
				this.preparedStatement.setDouble(3, exam.getPassingPercent());
				this.preparedStatement.setBoolean(4, true);
				this.preparedStatement.setBoolean(5, ((RealExam) exam).isActivated());
				this.preparedStatement.setBoolean(6, exam.isDraft());
				this.preparedStatement.setInt(7, examid);
			}else if(exam instanceof PracticeExam){
				update = "UPDATE tbl_exam SET exam_name=?,exam_type=?,exam_active=?,exam_is_draft=? WHERE exam_id=?";
				this.preparedStatement = this.connection.prepareStatement(update);
				
				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setBoolean(2, false);
				this.preparedStatement.setBoolean(3, true);
				this.preparedStatement.setBoolean(4, exam.isDraft());
				this.preparedStatement.setInt(5, examid);
			}
			int rows = this.preparedStatement.executeUpdate();
			success = (rows>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean deleteExam(String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String delete = "";
		this.connect();
		int id = 0;
		
		try{
			id = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try {
			this.connection.setAutoCommit(false);
			delete = "DELETE FROM tbl_exam WHERE exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(delete);
				
			this.preparedStatement.setInt(1, id);
			int rows = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (rows>0)?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean addStudentToExam(String studentid, String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String insert = "REPLACE INTO tbl_student_exam(student_id,exam_id) VALUES((SELECT user_id FROM tbl_user WHERE user_id=?),(SELECT exam_id FROM tbl_exam WHERE exam_id=?))";
		int id = 0;
		int eid = 0;
		
		try{
			id = Integer.parseInt(studentid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);
			
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, eid);
			
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean removeStudentFromExam(String studentid, String examid) throws ClassNotFoundException, SQLException{
		boolean success = false;
		String delete = "DELETE FROM tbl_student_exam WHERE student_id=(SELECT user_id FROM tbl_user WHERE user_id=?) AND exam_id=(SELECT exam_id FROM tbl_exam WHERE exam_id=?)";
		int id = 0;
		int eid = 0;
		
		try{
			id = Integer.parseInt(studentid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			id = 0;
		}
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(delete);
			
			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, eid);
			
			int row = this.preparedStatement.executeUpdate();
			
			this.connection.commit();
			success = (row>0)?true:false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public int retrieveMaxExamID() throws ClassNotFoundException, SQLException{
		int id = 0;
		
		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_exam'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(resultSet.next()){
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}
		finally{
			this.disconnect();
		}
		
		return id;
	}
	
	public int retrieveActivateCount(String examid) throws SQLException, ClassNotFoundException{
		int activatecount=0;
		String count = "SELETE activate_count FROM tbl_exam WHERE exam_id=?";
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try{
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(count);
			
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();
			
			while(this.resultSet.next()){
				activatecount = this.resultSet.getInt(1);
			}
		}catch(SQLException e){
			logger.error(e.getMessage(), e);
			throw e;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.disconnect();
		}
		
		return activatecount;
	}
	
	public boolean updateActivateCount(String examid) throws SQLException, ClassNotFoundException{
		boolean success = false;
		String update = "UPDATE tbl_exam SET activate_count = activate_count+1 WHERE exam_id=?";
		int eid = 0;
		
		try{
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		}catch(NumberFormatException e){
			eid = 0;
		}
		
		try{
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(update);
			
			this.preparedStatement.setInt(1, eid);
			int row = this.preparedStatement.executeUpdate();
			this.connection.commit();
			success = (row>0)?true:false;
		}catch(SQLException e){
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		}finally{
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		
		return success;
	}
	
	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	public ResultSet getResultSet() {
		return resultSet;
	}

	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}
}